#encoding: utf-8

=begin
同步crmeb产品，商家及文章索引数据库
同步crmeb熟人社会标签数据
=end

load 'rds_api.rb'

def clear_index
    sqls  = ["delete from crmeb.eb_search_index where 1=1;"]
    sqls += ["alter table crmeb.eb_search_index auto_increment = 0;"]
    sqls += ["update crmeb.eb_store_product set indexed_time = 0 where 1=1;"]
    sqls += ["update crmeb.eb_system_store set indexed_time = 0 where 1=1;"]
    sqls += ["update crmeb.eb_article set indexed_time = 0 where 1=1;"]
    sqls += ["delete from crmeb.eb_tag where type != -1;"]
    sqls += ["alter table crmeb.eb_tag auto_increment 10;"]
    commitTrans sqls
end

def sync_product_index
    sqls = []
    sqls += ["insert into crmeb.eb_search_index (
            type, spec, type_id,image, title, source_tags, description, news, fans, latest_fans, weight, hidden
        ) select
            'product',
            if ( product_type = 0, 'general', ( if ( product_type = 1, 'virtual', 'meetup') ) ) as spec,
            id, image,
            left(concat(name_prefix, store_name, name_suffix),64),
            tags, left(store_info,255),
            left(concat('¥', price, ' ', comment),64), 0, '{}', sort, !is_show
        from crmeb.eb_store_product where indexed_time = 0;"]
    sqls += ["update crmeb.eb_search_index set hidden = 1 where type = 'product' and type_id in (select id from crmeb.eb_store_product where is_del = 1 or is_show = 0);"]
    sqls += ["update crmeb.eb_store_product set indexed_time = #{Time.now.to_i} where indexed_time = 0;"]
    commitTrans sqls
    #TBD 可以考虑将通过比较收录时间，把收录时间比较旧的，也在数据库里做一次更新
end

def sync_merchant_index
    sqls = []
    sqls += ["insert into crmeb.eb_search_index (
                type, spec, type_id,image, title, source_tags, description, news, fans, latest_fans, weight, hidden
           ) select 
                'merchant',
                if ( is_parallel = 0, 'general', ( if ( is_parallel = 1, 'kol', 'site') ) ) as spec,
                id, image, name, concat(mig_tags,'|',tags) as source_tags, introduction, description, follow_count, '{}', sort, !is_show
            from crmeb.eb_system_store where indexed_time = 0;"]
    sqls += ["update crmeb.eb_search_index set hidden = 1 where type = 'merchant' and type_id in (select id from crmeb.eb_system_store where is_del = 1 or is_show = 0);"]
    sqls += ["update crmeb.eb_system_store set indexed_time = #{Time.now.to_i} where indexed_time = 0;"]
    commitTrans sqls
    #TBD 可以考虑将收录时间比较旧的，也在数据库里做一次更新
end

def normalize_article_tags
    usqls = []
    sql = "select id, tags from crmeb.eb_article where status = 1 and tags != '[]'"
    (queryRds(sql)).each do |q|
        tags = resolve_tags q['tags']
        usqls += ["update crmeb.eb_article set tags = '#{@rds.escape tags.to_json}' where id = #{q['id']}"]
    end
    commitTrans usqls
end

def sync_article_index
    sqls = []
    sqls += ["insert into crmeb.eb_search_index (
                type, spec, type_id,image, title, source_tags, description, news, fans, latest_fans, weight, hidden
          ) select
                'article',
                a.author, a.id, a.image_input, a.title, a.tags, a.synopsis, concat(m.name, from_unixtime(a.add_time,' 发表于 %m-%d %H:%i')), a.visit, '{}', 32730+a.sort+a.visit, a.hide
            from crmeb.eb_article a left join crmeb.eb_system_store m on (a.mer_id = m.id) where a.indexed_time = 0 and a.type = 'article';"]
    sqls += ["insert into crmeb.eb_search_index (
                type, spec, type_id,image, title, source_tags, description, news, fans, latest_fans, weight, hidden
          ) select
                'article',
                a.author, a.parent, a.image_input, a.title, a.tags, a.synopsis, concat(m.name, from_unixtime(a.add_time,' 评论于 %m-%d %H:%i')), a.visit, '{}', 32730+a.sort+a.visit, a.hide
            from crmeb.eb_article a left join crmeb.eb_system_store m on (a.mer_id = m.id) where a.indexed_time = 0 and a.type = 'comments';"]
    sqls += ["insert into crmeb.eb_search_index (
                type, spec, type_id,image, title, source_tags, description, news, fans, latest_fans, weight, hidden
          ) select
                'article',
                a.author, a.parent, a.image_input, a.title, a.tags, a.synopsis, concat(m.name, from_unixtime(a.add_time,' 转发于 %m-%d %H:%i')), a.visit, '{}', 32730+a.sort+a.visit, a.hide
            from crmeb.eb_article a left join crmeb.eb_system_store m on (a.mer_id = m.id) where a.indexed_time = 0 and a.type = 'forward';"]
    sqls += ["update crmeb.eb_search_index set hidden = 1 where type = 'article' and type_id in (select id from crmeb.eb_article where hide = 1 or status != 1);"]
    sqls += ["update crmeb.eb_article set indexed_time = #{Time.now.to_i} where indexed_time = 0;"]
    sqls += ["update crmeb.eb_search_index set image = 'https://oss.foodtrust.cn//71d24202203012251236734.gif' where image is NULL;"] #TBO
    commitTrans sqls
    #TBD 可以考虑将收录时间比较旧的，也在数据库里做一次更新
end

def update_space

    sql = "update crmeb.eb_search_index set space = 'mig' where type = 'merchant' and type_id in ( select id from crmeb.eb_system_store where is_parallel > 0 and is_del = 0);"
    queryRds sql

    sql = "update crmeb.eb_search_index set space = 'mig' where type = 'article' and type_id in ( select id from crmeb.eb_article where mer_id in (select id from crmeb.eb_system_store where is_parallel > 0 and is_del = 0));"
    queryRds sql

    sql = "update crmeb.eb_search_index set space = 'mig' where type = 'product' and type_id in ( select id from crmeb.eb_store_product where mer_id in (select id from crmeb.eb_system_store where is_parallel > 0 and is_del = 0));"
    queryRds sql

end

def update_index_tags tag_type

    sql = "select id, source_tags from crmeb.eb_search_index where type = '#{tag_type}' and source_tags is not null"
    pid = 5
    case tag_type
    when 'article'
        pid = 5
    when 'product'
        pid = 6
    when 'merchant'
        pid = 7
    else
        return
    end

    usqls = []
    isqls = []
    usedTags = []
    queryRds(sql).each do |q|
        sid = q['id']
        source_tags = q['source_tags'].to_s
        tags = resolve_tags source_tags
        usqls += ["update crmeb.eb_search_index set tags = '#{@rds.escape tags.to_json}' where id = #{sid}"] if !tags.empty?
        usedTags += tags
    end
    commitTrans usqls
    usedTags.each do |tag|
        usql = "update crmeb.eb_tag set weight = weight + 1  where name = '#{@rds.escape tag}' and pid = #{pid};"
        isql = "insert into crmeb.eb_tag(type, name, weight, pid, mer_id, is_del)
                    select 0, '#{@rds.escape tag}', 1, #{pid}, 0, 0
                from dual where not exists (select id from crmeb.eb_tag where name = '#{@rds.escape tag}' and pid = #{pid});"
        usqls += [usql]
        isqls += [isql]
    end
    commitTrans isqls
    commitTrans usqls

end

def update_search_tags
    sql = "SELECT * FROM crmeb.eb_search_keyword where from_mig = 1 order by weight desc limit 8;"
    pid = 8
    usqls = []
    isqls = []
    usedTags = []
    queryRds(sql).each do |q|
        source_tags = q['keyword'].to_s
        tags = resolve_tags source_tags
        usedTags += tags
    end
    commitTrans usqls
    usedTags.each do |tag|
        usql = "update crmeb.eb_tag set weight = weight + 1  where name = '#{@rds.escape tag}' and pid = #{pid};"
        isql = "insert into crmeb.eb_tag(type, name, weight, pid, mer_id, is_del)
                    select 0, '#{@rds.escape tag}', 1, #{pid}, 0, 0
                from dual where not exists (select id from crmeb.eb_tag where name = '#{@rds.escape tag}' and pid = #{pid});"
        usqls += [usql]
        isqls += [isql]
    end
    commitTrans isqls
    commitTrans usqls
end

def update_tags
  ['article','product','merchant'].each { |tt| update_index_tags tt }
  update_search_tags
end

def resolve_tags tags_string
    tobe_replaced = ['[',']',',','"',"'",'。','，','#','|','｜','、']
    tobe_replaced.each { |tbr| tags_string = tags_string.gsub(tbr,' ') }
    tags = tags_string.split(' ').uniq
    return tags
end

def jsonify_title content

    title = {}
    title.store "type", "input"
    title.store "value", content
    value = {}
    value.store "title", title
    return value.to_json

end

#数量不够要增加，如果数量少了，多余的要隐藏
def update_group_data_for gid, contents

    recs = []
    sql = "select * from crmeb.eb_system_group_data where gid=#{gid}"
    queryRds(sql).each { |q| recs += [q]  }

    sort = contents.size
    recs.size.times do |idx|
        rec = recs[idx]
        if  idx == contents.size #之前的记录多余故隐藏
            sqlu = "update crmeb.eb_system_group_data set status = 0 where id = #{rec['id']};"
            queryRds sqlu
        else
            v_str = jsonify_title contents[idx]
            sqlu = "update crmeb.eb_system_group_data set status = 1, value = '#{@rds.escape v_str}', sort = #{sort} , add_time = #{Time.now.to_i} where id = #{rec['id']};"
            queryRds sqlu
        end
        sort -= 1
    end

    #需要增加的部分
    sort = contents.size - recs.size
    (contents.size - recs.size).times do |idx|
        cdx = recs.size + idx
        v_str = jsonify_title contents[cdx]
        sqlu = "insert into crmeb.eb_system_group_data(gid,value,add_time,sort,status,mer_id) values(#{gid},'#{@rds.escape v_str}',#{Time.now.to_i},#{sort},1,0);"
        queryRds sqlu
        sort -= 1
    end

end

def get_mig_hot_keywords limit
    sql = "select * from crmeb.eb_search_keyword where from_mig = 1 and keyword not like '%酒%' and keyword not like '%盐%' order by weight desc limit #{limit};"
    hots = []
    queryRds(sql).each { |q| hots += [q['keyword']] }
    return hots
end

def get_native_hot_keywords limit
    sql = "select * from crmeb.eb_search_keyword where from_native = 1 and keyword not like '%酒%' and keyword not like '%盐%' order by weight desc limit #{limit};"
    hots = []
    queryRds(sql).each { |q| hots += [q['keyword']] }
    return hots
end

#更新原生空间的热搜关键词
def update_native_hot_keywords
    hots = (get_native_hot_keywords(10)).shuffle[0..4]
    update_group_data_for 56, hots
end

#平行狗蛋的MIG空间热搜词
def update_mig_hot_keywords
    hots = (get_mig_hot_keywords(10)).shuffle[0..4]
    update_group_data_for 64, hots
end

def get_tags_hash condition
    sql = "select * from crmeb.eb_search_index where #{condition} and source_tags != '' and source_tags is not null;"
    atags = []
    asort = {}
    queryRds(sql).each do |q| 
        tags = JSON.parse q['tags']
        tags.each do |tag|
            if asort[tag].nil?
                asort.store tag, 1
            else
                asort.store tag, asort[tag] + 1
            end
        end
        atags += tags
        atags = atags.uniq
        atags -= ['pickup'] #去除系统保留tag
    end
    #return atags
    return asort
end

def update_mig_hot_tags
    tags = get_tags_hash("space = 'mig'").sort { |a, b|  b[1]<=>a[1] }
    tags = tags[0..8]
    hots = []
    tags.each { |tag| hots += [tag[0]] }
    update_group_data_for 66, hots
    return hots
end

def update_native_hot_tags
    tags = get_tags_hash(" 1=1 ").sort { |a, b|  b[1]<=>a[1] }
    tags = tags[0..8]
    hots = []
    tags.each { |tag| hots += [tag[0]] }
    update_group_data_for 65, hots
    return hots
end

#修正已存在文章的tags
def fix_article_tags
  sql = "select id, tags from crmeb.eb_article"
  usqls = []
  (queryRds(sql)).each do |q|
      id = q['id']
      tags = resolve_tags q['tags'].to_s
      usqls += ["update crmeb.eb_article set tags = '#{@rds.escape tags.to_json}' where id = #{id}"]
  end
  commitTrans usqls
end

#修复favored数据，排除默认模板数据，排除错误类型，以及自己关注自己
def fix_favored

  usqls = []
  sql = "select * from crmeb.eb_system_store where owner_uid > 0 and is_parallel > 0"

  (queryRds(sql)).each do |q|
    mer_id = q['id']
    owner_uid = q['owner_uid']
    mer_type = q['is_parallel']
    favored_kols = JSON.parse(q['favored_kols']).uniq
    favored_locations = JSON.parse(q['favored_locations']).uniq

    if favored_kols != []
      favored_kols_string = favored_kols.join(',')
      favored_kols = []
      isql = "select id from crmeb.eb_system_store where id in (#{favored_kols_string}) and is_parallel = 1 and id != #{mer_id}"
      (queryRds(isql)).each { |i| favored_kols += [i['id']] }
    end

    if favored_locations != []
      favored_locations_string = favored_locations.join(',')
      favored_locations = []
      isql = "select id from crmeb.eb_system_store where id in (#{favored_locations_string}) and is_parallel = 2 and id != #{mer_id}"
      (queryRds(isql)).each { |i| favored_locations += [i['id']] }
    end

    if JSON.parse(q['favored_kols']).uniq != favored_kols || JSON.parse(q['favored_locations']).uniq != favored_locations
      puts "favored_kols: #{q['favored_kols']} => #{favored_kols}"
      puts "favored_locs: #{q['favored_locations']} => #{favored_locations}"
      usql = "update crmeb.eb_system_store set favored_kols = '#{favored_kols}', favored_locations = '#{favored_locations}' where id = #{mer_id}"
      usqls += [usql]
    end

  end

  commitTrans usqls

end

#根据关注信息修复mer_id名下的favor关系
def recover_favored

  usqls = []
  sql = "select * from crmeb.eb_system_store where owner_uid > 0 and is_parallel > 0 and owner_uid > 0"

  (queryRds(sql)).each do |q|

    mer_id = q['id']
    owner_uid = q['owner_uid']
    favored_kols = JSON.parse(q['favored_kols']).uniq
    favored_locations = JSON.parse(q['favored_locations']).uniq

    isql = "select r.uid as owner_uid, r.product_id as peer_merid, s.is_parallel as peer_type from crmeb.eb_store_product_relation r left join crmeb.eb_system_store s on (r.product_id = s.id) where r.uid = #{owner_uid} and r.category = 'merchant'"
    (queryRds(isql)).each do |i|
      favored_kols += [i['peer_merid']] if i['peer_merid'] != mer_id && i['peer_type'] == 1
      favored_locations += [i['peer_merid']] if i['peer_merid'] != mer_id && i['peer_type'] == 2
    end
    favored_kols = favored_kols.uniq
    favored_locations = favored_locations.uniq

    if JSON.parse(q['favored_kols']).uniq.sort != favored_kols.sort || JSON.parse(q['favored_locations']).uniq.sort != favored_locations.sort
      puts "[merid]#{sprintf('%3d',mer_id)} favored_kols: #{q['favored_kols']} => #{favored_kols} favored_locs: #{q['favored_locations']} => #{favored_locations}"
      usql = "update crmeb.eb_system_store set favored_kols = '#{favored_kols}', favored_locations = '#{favored_locations}' where id = #{mer_id}"
      usqls += [usql]
    end

  end

  commitTrans usqls

end

def sync_merchant_area
  sql = "update crmeb.eb_system_store as s inner join crmeb.eb_user as u on (s.owner_uid = u.uid) set s.area_id = u.area_id where s.owner_uid > 0 and u.area_id > 0"
  queryRds sql
end

#@rds_mode = 'debug'
#clear_index
sync_product_index
sync_merchant_index
normalize_article_tags
sync_article_index
update_space
update_tags
update_native_hot_keywords  #更新原生空间的前五个热搜关键词缓存
update_mig_hot_keywords     #平行狗蛋的MIG空间前五个热搜词的缓存
update_mig_hot_tags         #更新最热的八个标签
update_native_hot_tags      #
sync_merchant_area
